﻿Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Collections
Imports extranet.BE
Imports MySql.Data.MySqlClient

Public Class DAAlternativa
  Private objBD As New BancoDeDados
  Public objQuery As New BEQuery

  Sub New()
  End Sub

  Sub New(ByVal booCondicaoSimilar As Boolean)
    objQuery.Where.CondicaoSimilar = booCondicaoSimilar
  End Sub

  Public Sub Cadastra(ByRef objBEAlternativa As BEAlternativa)
    Try
      objBD.duplicaAspasSimples(objBEAlternativa, GetType(BEAlternativa))
      objBD.ExecutaNonQuery("INSERT INTO ext_alternativas_questao (questaoid, texto, alternativacerta) " & _
                            "VALUES ('" & objBEAlternativa.QuestaoId & "', '" & _
                                          objBEAlternativa.Texto & "', '" & _
                                          IIf(objBEAlternativa.RespostaCerta, 1, 0) & "');" & _
                            "SELECT LAST_INSERT_ID();")
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAAlternativa.Cadastra(): " & ex.Message)
    End Try
  End Sub

  Public Sub Atualiza(ByVal objBEAlternativa As BEAlternativa)
    Try
      objBD.duplicaAspasSimples(objBEAlternativa, GetType(BEAlternativa))
      objBD.ExecutaNonQuery("UPDATE ext_alternativas_questao " & _
                            "SET questaoid='" & objBEAlternativa.QuestaoId & "', " & _
                                "texto='" & objBEAlternativa.Texto & "', " & _
                                "alternativacerta='" & IIf(objBEAlternativa.RespostaCerta, 1, 0) & "' " & _
                            "WHERE id=" & objBEAlternativa.Id & ";")
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAAlternativa.Atualiza(): " & ex.Message)
    End Try
  End Sub

  Public Sub Exclui(ByVal objBEAlternativa As BEAlternativa)
    Try
      objBD.ExecutaNonQuery("DELETE FROM ext_alternativas_questao WHERE alternativaid='" & objBEAlternativa.Id & "';")
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAAlternativa.Exclui(): " & ex.Message)
    End Try
  End Sub

  Public Function Seleciona(ByVal objBEAlternativa As BEAlternativa) As List(Of BEAlternativa)
    Dim objBEAlternativas As List(Of BEAlternativa) = New List(Of BEAlternativa)
    Try
      objBD.duplicaAspasSimples(objBEAlternativa, GetType(BEAlternativa))
      objQuery._Select = "SELECT * FROM ext_alternativas_questao"
      If objBEAlternativa IsNot Nothing Then
        If objBEAlternativa.Id > 0 Then objQuery.Where.AdicionarCondicaoWhere("alternativaid", objBEAlternativa.Id, False)
        If objBEAlternativa.QuestaoId > 0 Then objQuery.Where.AdicionarCondicaoWhere("questaoid", objBEAlternativa.QuestaoId, False)
        objQuery.Where.AdicionarCondicaoWhere("textoalternativa", objBEAlternativa.Texto)
      End If
      objBEAlternativas = SelecionaAlternativas()
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAAlternativa.Seleciona(): " & ex.Message)
    End Try
    Return objBEAlternativas
  End Function

  Private Function SelecionaAlternativas() As List(Of BEAlternativa)
    Dim objBEAlternativas As List(Of BEAlternativa) = New List(Of BEAlternativa)
    Dim objBEAlternativa As BEAlternativa
    Try
      Using objSQLCommand As MySqlCommand = New MySqlCommand(objQuery.Query, BancoDeDados.ConnectionInstance())
        Using objSQLDReader As MySqlDataReader = objSQLCommand.ExecuteReader()
          While objSQLDReader.Read()
            objBEAlternativa = New BEAlternativa()
            With objBEAlternativa
              .Id = objSQLDReader("id")
              .QuestaoId = objSQLDReader("questaoid")
              .Texto = objSQLDReader("texto")
              .RespostaCerta = objSQLDReader("alternativacerta")
            End With
            objBEAlternativas.Add(objBEAlternativa)
          End While
        End Using
      End Using
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAAlternativa.SelecionaAlternativas(): " & ex.Message)
    End Try
    Return objBEAlternativas
  End Function

  Public Function SelecionarRespostaQuestao(ByVal intIdCandidato As Integer, ByVal intIdProva As Integer, _
                                                   ByVal intIdQuestao As Integer) As List(Of BEAlternativa)
    Dim objBEAlternativas As List(Of BEAlternativa) = New List(Of BEAlternativa)
    Try
      objQuery._Select = "SELECT * FROM ext_alternativas_questao " & _
                         "WHERE (questaoid=" & intIdQuestao & " AND alternativaCerta=1) OR " & _
                               "(id IN (SELECT alternativaid FROM ext_notas_candidato " & _
                                       "WHERE candidatoId=" & intIdCandidato & " AND " & _
                                             "provaid=" & intIdProva & " AND " & _
                                             "questaoid=" & intIdQuestao & "))"
      objBEAlternativas = SelecionaAlternativas()
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAAlternativa.SelecionarRespostaQuestao(): " & ex.Message)
    End Try
    Return objBEAlternativas
  End Function

  Public Function SelecionarRespostaDissertativa(ByVal intIdCandidato As Integer, ByVal intIdProva As Integer, _
                                                 ByVal intIdQuestao As Integer) As String
    Dim strRespostaDissertativa As String = ""
    Try
      objQuery._Select = "SELECT respostaDissertativa FROM ext_notas_candidato " & _
                         "WHERE candidatoId=" & intIdCandidato & " AND " & _
                               "provaid=" & intIdProva & " AND " & _
                               "questaoid=" & intIdQuestao
      Using objSQLCommand As MySqlCommand = New MySqlCommand(objQuery.Query, BancoDeDados.ConnectionInstance())
        Using objSQLDReader As MySqlDataReader = objSQLCommand.ExecuteReader()
          While objSQLDReader.Read()
            strRespostaDissertativa = objSQLDReader("respostaDissertativa")
          End While
        End Using
      End Using
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAAlternativa.SelecionarRespostaDissertativa(): " & ex.Message)
    End Try
    Return strRespostaDissertativa
  End Function

  Public Function StatusRespostaDissertativa(ByVal intIdCandidato As Integer, ByVal intIdProva As Integer, _
                                             ByVal intIdQuestao As Integer) As Integer
    Dim intRespostaDissertativaCerta As Integer = 0
    Try
      objQuery._Select = "SELECT respostaDissertativaCerta FROM ext_notas_candidato " & _
                         "WHERE candidatoId=" & intIdCandidato & " AND " & _
                               "provaid=" & intIdProva & " AND " & _
                               "questaoid=" & intIdQuestao
      Using objSQLCommand As MySqlCommand = New MySqlCommand(objQuery.Query, BancoDeDados.ConnectionInstance())
        Using objSQLDReader As MySqlDataReader = objSQLCommand.ExecuteReader()
          While objSQLDReader.Read()
            intRespostaDissertativaCerta = objSQLDReader("respostaDissertativaCerta")
          End While
        End Using
      End Using
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAAlternativa.StatusRespostaDissertativa(): " & ex.Message)
    End Try
    Return intRespostaDissertativaCerta
  End Function

  Public Function SelecionarRespostaQuestao(ByVal strAluno As String, ByVal intIdProva As Integer, _
                                                   ByVal intIdQuestao As Integer) As List(Of BEAlternativa)
    Dim objBEAlternativas As List(Of BEAlternativa) = New List(Of BEAlternativa)
    Try
      objQuery._Select = "SELECT * FROM ext_alternativas_questao " & _
                         "WHERE (questaoid=" & intIdQuestao & " AND alternativaCerta=1) OR " & _
                               "(id IN (SELECT alternativaid FROM ext_respostas_aluno " & _
                                       "WHERE usuario='" & strAluno & "' AND " & _
                                             "provaid=" & intIdProva & " AND " & _
                                             "questaoid=" & intIdQuestao & "))"
      objBEAlternativas = SelecionaAlternativas()
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAAlternativa.SelecionarRespostaQuestao(): " & ex.Message)
    End Try
    Return objBEAlternativas
  End Function

  Public Function StatusRespostaDissertativa(ByVal strAluno As String, ByVal intIdProva As Integer, _
                                             ByVal intIdQuestao As Integer) As Integer
    Dim intRespostaDissertativaCerta As Integer = 0
    Try
      objQuery._Select = "SELECT respostaDissertativaCerta FROM ext_respostas_aluno " & _
                         "WHERE usuario='" & strAluno & "' AND " & _
                               "provaid=" & intIdProva & " AND " & _
                               "questaoid=" & intIdQuestao
      Using objSQLCommand As MySqlCommand = New MySqlCommand(objQuery.Query, BancoDeDados.ConnectionInstance())
        Using objSQLDReader As MySqlDataReader = objSQLCommand.ExecuteReader()
          While objSQLDReader.Read()
            intRespostaDissertativaCerta = objSQLDReader("respostaDissertativaCerta")
          End While
        End Using
      End Using
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAAlternativa.StatusRespostaDissertativa(): " & ex.Message)
    End Try
    Return intRespostaDissertativaCerta
  End Function

  Public Function SelecionarRespostaDissertativa(ByVal strAluno As String, ByVal intIdProva As Integer, _
                                                 ByVal intIdQuestao As Integer) As String
    Dim strRespostaDissertativa As String = ""
    Try
      objQuery._Select = "SELECT respostaDissertativa FROM ext_respostas_aluno " & _
                         "WHERE usuario='" & strAluno & "' AND " & _
                               "provaid=" & intIdProva & " AND " & _
                               "questaoid=" & intIdQuestao
      Using objSQLCommand As MySqlCommand = New MySqlCommand(objQuery.Query, BancoDeDados.ConnectionInstance())
        Using objSQLDReader As MySqlDataReader = objSQLCommand.ExecuteReader()
          While objSQLDReader.Read()
            strRespostaDissertativa = objSQLDReader("respostaDissertativa")
          End While
        End Using
      End Using
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAAlternativa.SelecionarRespostaDissertativa(): " & ex.Message)
    End Try
    Return strRespostaDissertativa
  End Function

End Class
